{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 资本资产定价模型 Capital Asset Pricing Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np # 数据处理最重要的模块\n",
    "import pandas as pd # 数据处理最重要的模块\n",
    "import scipy.stats as stats # 统计模块\n",
    "import scipy\n",
    "# import pymysql  # 导入数据库模块\n",
    "\n",
    "from datetime import datetime # 时间模块\n",
    "import statsmodels.formula.api as smf  # OLS regression\n",
    "\n",
    "# import pyreadr # read RDS file\n",
    "\n",
    "from matplotlib import style\n",
    "import matplotlib.pyplot as plt  # 画图模块\n",
    "import matplotlib.dates as mdates\n",
    "\n",
    "from matplotlib.font_manager import FontProperties # 作图中文\n",
    "from pylab import mpl\n",
    "#mpl.rcParams['font.sans-serif'] = ['SimHei']\n",
    "#plt.rcParams['font.family'] = 'Times New Roman'\n",
    "\n",
    "#输出矢量图 渲染矢量图\n",
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format = 'svg'\n",
    "\n",
    "from IPython.core.interactiveshell import InteractiveShell # jupyter运行输出的模块\n",
    "#显示每一个运行结果\n",
    "InteractiveShell.ast_node_interactivity = 'all'\n",
    "\n",
    "#设置行不限制数量\n",
    "#pd.set_option('display.max_rows',None)\n",
    "\n",
    "#设置列不限制数量\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>38.34</td>\n",
       "      <td>2</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.016010e+09</td>\n",
       "      <td>1.859497e+09</td>\n",
       "      <td>20.739149</td>\n",
       "      <td>21.343572</td>\n",
       "      <td>-0.122253</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.128345</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-06-30</td>\n",
       "      <td>33.99</td>\n",
       "      <td>3</td>\n",
       "      <td>23.0</td>\n",
       "      <td>9.007350e+08</td>\n",
       "      <td>1.648521e+09</td>\n",
       "      <td>20.618722</td>\n",
       "      <td>21.223144</td>\n",
       "      <td>-0.113459</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>-0.137013</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-07-31</td>\n",
       "      <td>29.54</td>\n",
       "      <td>4</td>\n",
       "      <td>16.0</td>\n",
       "      <td>7.828100e+08</td>\n",
       "      <td>1.432695e+09</td>\n",
       "      <td>20.478401</td>\n",
       "      <td>21.082823</td>\n",
       "      <td>-0.130921</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.137013</td>\n",
       "      <td>-0.417680</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-08-31</td>\n",
       "      <td>15.00</td>\n",
       "      <td>5</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.748338e+08</td>\n",
       "      <td>1.346275e+09</td>\n",
       "      <td>20.329977</td>\n",
       "      <td>21.020607</td>\n",
       "      <td>-0.411588</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.417680</td>\n",
       "      <td>-0.039425</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-09-30</td>\n",
       "      <td>14.50</td>\n",
       "      <td>6</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6.523394e+08</td>\n",
       "      <td>1.301399e+09</td>\n",
       "      <td>20.296075</td>\n",
       "      <td>20.986706</td>\n",
       "      <td>-0.033333</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.039425</td>\n",
       "      <td>0.849080</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752023</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>13.56</td>\n",
       "      <td>24</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.955351e+09</td>\n",
       "      <td>1.054667e+10</td>\n",
       "      <td>22.797111</td>\n",
       "      <td>23.079076</td>\n",
       "      <td>-0.025862</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752024</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>14.54</td>\n",
       "      <td>25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.530295e+09</td>\n",
       "      <td>1.130889e+10</td>\n",
       "      <td>22.866890</td>\n",
       "      <td>23.148855</td>\n",
       "      <td>0.072271</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752025</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>13.85</td>\n",
       "      <td>26</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.125488e+09</td>\n",
       "      <td>1.077222e+10</td>\n",
       "      <td>22.818272</td>\n",
       "      <td>23.100237</td>\n",
       "      <td>-0.047455</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752026</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>13.48</td>\n",
       "      <td>27</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.908417e+09</td>\n",
       "      <td>1.048444e+10</td>\n",
       "      <td>22.791193</td>\n",
       "      <td>23.073159</td>\n",
       "      <td>-0.026715</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>752027</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>14.89</td>\n",
       "      <td>28</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.735632e+09</td>\n",
       "      <td>1.158111e+10</td>\n",
       "      <td>22.890676</td>\n",
       "      <td>23.172641</td>\n",
       "      <td>0.104599</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>709883 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month  price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "1       000001 1991-05-31  38.34     2  24.0   1.016010e+09  1.859497e+09   \n",
       "2       000001 1991-06-30  33.99     3  23.0   9.007350e+08  1.648521e+09   \n",
       "3       000001 1991-07-31  29.54     4  16.0   7.828100e+08  1.432695e+09   \n",
       "4       000001 1991-08-31  15.00     5  15.0   6.748338e+08  1.346275e+09   \n",
       "5       000001 1991-09-30  14.50     6  24.0   6.523394e+08  1.301399e+09   \n",
       "...        ...        ...    ...   ...   ...            ...           ...   \n",
       "752023  605599 2023-08-31  13.56    24  23.0   7.955351e+09  1.054667e+10   \n",
       "752024  605599 2023-09-30  14.54    25  20.0   8.530295e+09  1.130889e+10   \n",
       "752025  605599 2023-10-31  13.85    26  17.0   8.125488e+09  1.077222e+10   \n",
       "752026  605599 2023-11-30  13.48    27  22.0   7.908417e+09  1.048444e+10   \n",
       "752027  605599 2023-12-31  14.89    28  21.0   8.735632e+09  1.158111e+10   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \n",
       "1       20.739149  21.343572 -0.122253  0.006092 -0.128345 -0.119551  1  \n",
       "2       20.618722  21.223144 -0.113459  0.006092 -0.119551 -0.137013  1  \n",
       "3       20.478401  21.082823 -0.130921  0.006092 -0.137013 -0.417680  1  \n",
       "4       20.329977  21.020607 -0.411588  0.006092 -0.417680 -0.039425  1  \n",
       "5       20.296075  20.986706 -0.033333  0.006092 -0.039425  0.849080  1  \n",
       "...           ...        ...       ...       ...       ...       ... ..  \n",
       "752023  22.797111  23.079076 -0.025862  0.001241 -0.027103  0.071030  1  \n",
       "752024  22.866890  23.148855  0.072271  0.001241  0.071030 -0.048696  1  \n",
       "752025  22.818272  23.100237 -0.047455  0.001241 -0.048696 -0.027956  1  \n",
       "752026  22.791193  23.073159 -0.026715  0.001241 -0.027956  0.103358  1  \n",
       "752027  22.890676  23.172641  0.104599  0.001241  0.103358       NaN  1  \n",
       "\n",
       "[709883 rows x 14 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cross = pd.read_csv('datasets/ret_mon_python2023.csv')\n",
    "from pandas.tseries.offsets import MonthEnd\n",
    "cross['month'] = pd.to_datetime(cross['month'], format='%Y-%m-%d') + MonthEnd(1)\n",
    "# 补齐股票代码 如果不满6位 在前面补上0\n",
    "cross['Stkcd'] = cross['Stkcd'].apply(lambda x: '{:0>6}'.format(x)) # 6位股票代码\n",
    "cross['w'] = 1\n",
    "cross = cross.dropna(subset=['ret'])\n",
    "cross"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 故事整体收益率：上证指数\n",
    "- 股票市场整体收益率：所有股票来计算（包含0,3,6开头）\n",
    "- 3开头是深圳创业板 2009开始"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$$\n",
    "MKT_t=R_{m,t}-R_{f,t}\n",
    "$$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>MarketR</th>\n",
       "      <th>MarketR_e</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>MKT</th>\n",
       "      <th>ret_e</th>\n",
       "      <th>marketret3</th>\n",
       "      <th>marketret6</th>\n",
       "      <th>marketret12</th>\n",
       "      <th>Q</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1991-01-31</td>\n",
       "      <td>0.029998</td>\n",
       "      <td>0.036554</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.023068</td>\n",
       "      <td>0.029624</td>\n",
       "      <td>-0.084127</td>\n",
       "      <td>-0.305662</td>\n",
       "      <td>0.254049</td>\n",
       "      <td>1991 Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1991-02-28</td>\n",
       "      <td>0.010203</td>\n",
       "      <td>0.021860</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>0.003273</td>\n",
       "      <td>0.014930</td>\n",
       "      <td>-0.183573</td>\n",
       "      <td>-0.384745</td>\n",
       "      <td>0.241492</td>\n",
       "      <td>1991 Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1991-03-31</td>\n",
       "      <td>-0.099663</td>\n",
       "      <td>-0.060425</td>\n",
       "      <td>0.006930</td>\n",
       "      <td>-0.106593</td>\n",
       "      <td>-0.067355</td>\n",
       "      <td>-0.252928</td>\n",
       "      <td>-0.445049</td>\n",
       "      <td>0.288857</td>\n",
       "      <td>1991 Q1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1991-04-30</td>\n",
       "      <td>-0.079652</td>\n",
       "      <td>-0.031449</td>\n",
       "      <td>0.006651</td>\n",
       "      <td>-0.086303</td>\n",
       "      <td>-0.038100</td>\n",
       "      <td>-0.234776</td>\n",
       "      <td>-0.394937</td>\n",
       "      <td>0.691749</td>\n",
       "      <td>1991 Q2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>-0.074521</td>\n",
       "      <td>0.005375</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.080613</td>\n",
       "      <td>-0.000717</td>\n",
       "      <td>-0.236294</td>\n",
       "      <td>0.181673</td>\n",
       "      <td>1.542701</td>\n",
       "      <td>1991 Q2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>-0.055570</td>\n",
       "      <td>-0.041538</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.056811</td>\n",
       "      <td>-0.042779</td>\n",
       "      <td>-0.089387</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023 Q3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>392</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>-0.005689</td>\n",
       "      <td>0.000357</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.006930</td>\n",
       "      <td>-0.000884</td>\n",
       "      <td>-0.033855</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023 Q3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>393</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>-0.026321</td>\n",
       "      <td>-0.009959</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027562</td>\n",
       "      <td>-0.011200</td>\n",
       "      <td>-0.047817</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023 Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>0.001790</td>\n",
       "      <td>0.034280</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.000549</td>\n",
       "      <td>0.033039</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023 Q4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-0.020003</td>\n",
       "      <td>-0.020632</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.021244</td>\n",
       "      <td>-0.021873</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023 Q4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>396 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         month   MarketR  MarketR_e   rfmonth       MKT     ret_e  marketret3  \\\n",
       "0   1991-01-31  0.029998   0.036554  0.006930  0.023068  0.029624   -0.084127   \n",
       "1   1991-02-28  0.010203   0.021860  0.006930  0.003273  0.014930   -0.183573   \n",
       "2   1991-03-31 -0.099663  -0.060425  0.006930 -0.106593 -0.067355   -0.252928   \n",
       "3   1991-04-30 -0.079652  -0.031449  0.006651 -0.086303 -0.038100   -0.234776   \n",
       "4   1991-05-31 -0.074521   0.005375  0.006092 -0.080613 -0.000717   -0.236294   \n",
       "..         ...       ...        ...       ...       ...       ...         ...   \n",
       "391 2023-08-31 -0.055570  -0.041538  0.001241 -0.056811 -0.042779   -0.089387   \n",
       "392 2023-09-30 -0.005689   0.000357  0.001241 -0.006930 -0.000884   -0.033855   \n",
       "393 2023-10-31 -0.026321  -0.009959  0.001241 -0.027562 -0.011200   -0.047817   \n",
       "394 2023-11-30  0.001790   0.034280  0.001241  0.000549  0.033039         NaN   \n",
       "395 2023-12-31 -0.020003  -0.020632  0.001241 -0.021244 -0.021873         NaN   \n",
       "\n",
       "     marketret6  marketret12        Q  \n",
       "0     -0.305662     0.254049  1991 Q1  \n",
       "1     -0.384745     0.241492  1991 Q1  \n",
       "2     -0.445049     0.288857  1991 Q1  \n",
       "3     -0.394937     0.691749  1991 Q2  \n",
       "4      0.181673     1.542701  1991 Q2  \n",
       "..          ...          ...      ...  \n",
       "391         NaN          NaN  2023 Q3  \n",
       "392         NaN          NaN  2023 Q3  \n",
       "393         NaN          NaN  2023 Q4  \n",
       "394         NaN          NaN  2023 Q4  \n",
       "395         NaN          NaN  2023 Q4  \n",
       "\n",
       "[396 rows x 10 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Market_ret = pd.read_excel('datasets/Marketret_mon_stock2023.xlsx')\n",
    "Market_ret['month'] = pd.to_datetime(Market_ret['month'],format='%b %Y') + MonthEnd(1)\n",
    "Market_ret.rename(columns={'ret':'MKT'}, inplace=True)\n",
    "Market_ret"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "      <th>MKT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>38.34</td>\n",
       "      <td>2</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.016010e+09</td>\n",
       "      <td>1.859497e+09</td>\n",
       "      <td>20.739149</td>\n",
       "      <td>21.343572</td>\n",
       "      <td>-0.122253</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.128345</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.080613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-06-30</td>\n",
       "      <td>33.99</td>\n",
       "      <td>3</td>\n",
       "      <td>23.0</td>\n",
       "      <td>9.007350e+08</td>\n",
       "      <td>1.648521e+09</td>\n",
       "      <td>20.618722</td>\n",
       "      <td>21.223144</td>\n",
       "      <td>-0.113459</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.119551</td>\n",
       "      <td>-0.137013</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.085440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-07-31</td>\n",
       "      <td>29.54</td>\n",
       "      <td>4</td>\n",
       "      <td>16.0</td>\n",
       "      <td>7.828100e+08</td>\n",
       "      <td>1.432695e+09</td>\n",
       "      <td>20.478401</td>\n",
       "      <td>21.082823</td>\n",
       "      <td>-0.130921</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.137013</td>\n",
       "      <td>-0.417680</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.088189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-08-31</td>\n",
       "      <td>15.00</td>\n",
       "      <td>5</td>\n",
       "      <td>15.0</td>\n",
       "      <td>6.748338e+08</td>\n",
       "      <td>1.346275e+09</td>\n",
       "      <td>20.329977</td>\n",
       "      <td>21.020607</td>\n",
       "      <td>-0.411588</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.417680</td>\n",
       "      <td>-0.039425</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.090277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-09-30</td>\n",
       "      <td>14.50</td>\n",
       "      <td>6</td>\n",
       "      <td>24.0</td>\n",
       "      <td>6.523394e+08</td>\n",
       "      <td>1.301399e+09</td>\n",
       "      <td>20.296075</td>\n",
       "      <td>20.986706</td>\n",
       "      <td>-0.033333</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.039425</td>\n",
       "      <td>0.849080</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.031046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709878</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>13.56</td>\n",
       "      <td>24</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.955351e+09</td>\n",
       "      <td>1.054667e+10</td>\n",
       "      <td>22.797111</td>\n",
       "      <td>23.079076</td>\n",
       "      <td>-0.025862</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.056811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709879</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>14.54</td>\n",
       "      <td>25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.530295e+09</td>\n",
       "      <td>1.130889e+10</td>\n",
       "      <td>22.866890</td>\n",
       "      <td>23.148855</td>\n",
       "      <td>0.072271</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.006930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709880</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>13.85</td>\n",
       "      <td>26</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.125488e+09</td>\n",
       "      <td>1.077222e+10</td>\n",
       "      <td>22.818272</td>\n",
       "      <td>23.100237</td>\n",
       "      <td>-0.047455</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.027562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709881</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>13.48</td>\n",
       "      <td>27</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.908417e+09</td>\n",
       "      <td>1.048444e+10</td>\n",
       "      <td>22.791193</td>\n",
       "      <td>23.073159</td>\n",
       "      <td>-0.026715</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709882</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>14.89</td>\n",
       "      <td>28</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.735632e+09</td>\n",
       "      <td>1.158111e+10</td>\n",
       "      <td>22.890676</td>\n",
       "      <td>23.172641</td>\n",
       "      <td>0.104599</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.021244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>709883 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month  price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "0       000001 1991-05-31  38.34     2  24.0   1.016010e+09  1.859497e+09   \n",
       "1       000001 1991-06-30  33.99     3  23.0   9.007350e+08  1.648521e+09   \n",
       "2       000001 1991-07-31  29.54     4  16.0   7.828100e+08  1.432695e+09   \n",
       "3       000001 1991-08-31  15.00     5  15.0   6.748338e+08  1.346275e+09   \n",
       "4       000001 1991-09-30  14.50     6  24.0   6.523394e+08  1.301399e+09   \n",
       "...        ...        ...    ...   ...   ...            ...           ...   \n",
       "709878  605599 2023-08-31  13.56    24  23.0   7.955351e+09  1.054667e+10   \n",
       "709879  605599 2023-09-30  14.54    25  20.0   8.530295e+09  1.130889e+10   \n",
       "709880  605599 2023-10-31  13.85    26  17.0   8.125488e+09  1.077222e+10   \n",
       "709881  605599 2023-11-30  13.48    27  22.0   7.908417e+09  1.048444e+10   \n",
       "709882  605599 2023-12-31  14.89    28  21.0   8.735632e+09  1.158111e+10   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \\\n",
       "0       20.739149  21.343572 -0.122253  0.006092 -0.128345 -0.119551  1   \n",
       "1       20.618722  21.223144 -0.113459  0.006092 -0.119551 -0.137013  1   \n",
       "2       20.478401  21.082823 -0.130921  0.006092 -0.137013 -0.417680  1   \n",
       "3       20.329977  21.020607 -0.411588  0.006092 -0.417680 -0.039425  1   \n",
       "4       20.296075  20.986706 -0.033333  0.006092 -0.039425  0.849080  1   \n",
       "...           ...        ...       ...       ...       ...       ... ..   \n",
       "709878  22.797111  23.079076 -0.025862  0.001241 -0.027103  0.071030  1   \n",
       "709879  22.866890  23.148855  0.072271  0.001241  0.071030 -0.048696  1   \n",
       "709880  22.818272  23.100237 -0.047455  0.001241 -0.048696 -0.027956  1   \n",
       "709881  22.791193  23.073159 -0.026715  0.001241 -0.027956  0.103358  1   \n",
       "709882  22.890676  23.172641  0.104599  0.001241  0.103358       NaN  1   \n",
       "\n",
       "             MKT  \n",
       "0      -0.080613  \n",
       "1      -0.085440  \n",
       "2      -0.088189  \n",
       "3      -0.090277  \n",
       "4      -0.031046  \n",
       "...          ...  \n",
       "709878 -0.056811  \n",
       "709879 -0.006930  \n",
       "709880 -0.027562  \n",
       "709881  0.000549  \n",
       "709882 -0.021244  \n",
       "\n",
       "[709883 rows x 15 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cross = pd.merge(cross,Market_ret[['month','MKT']],left_on='month',right_on='month',how='left')\n",
    "cross"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "def jiaqilai(x,y,z):\n",
    "    return x+y-z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jiaqilai(1,2,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "import statsmodels.api as sm\n",
    "\n",
    "\n",
    "def rolling_beta_per_stock(data,window_months=60):\n",
    "    betas=[]\n",
    "    months=[]\n",
    "    Stkcds=[]\n",
    "    data_counts=[]\n",
    "\n",
    "\n",
    "    # 按股票分组\n",
    "    grouped=data.groupby('Stkcd')\n",
    "\n",
    "\n",
    "    for Stkcd,group in grouped:\n",
    "        group=group.set_index('month').sort_index()\n",
    "        end_months=group.index.unique()\n",
    "        for end_month in end_months:\n",
    "            start_month=end_month-pd.DateOffset(months=window_months)\n",
    "            window_data=group.loc[start_month:end_month]\n",
    "\n",
    "\n",
    "            if len(window_data)>0:\n",
    "                X=sm.add_constant(window_data['MKT'])#添加截距项\n",
    "                y=window_data['ret']\n",
    "                model=sm.OLS(y,X)\n",
    "\n",
    "\n",
    "                beta=model.fit().params['MKT']\n",
    "                betas.append(beta)\n",
    "                months.append(end_month)\n",
    "                Stkcds.append(Stkcd)\n",
    "                data_counts.append(len(window_data))\n",
    "    return pd.DataFrame({'Stkcd':Stkcds,'month':months,'beta':betas,'data_count':data_counts})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>beta</th>\n",
       "      <th>data_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>1.592113</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-06-30</td>\n",
       "      <td>-1.821836</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-07-31</td>\n",
       "      <td>0.807037</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-08-31</td>\n",
       "      <td>23.378197</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-09-30</td>\n",
       "      <td>3.358613</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709878</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>1.040511</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709879</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>1.038189</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709880</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>1.052476</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709881</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>1.047631</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709882</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>1.017220</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>709883 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month       beta  data_count\n",
       "0       000001 1991-05-31   1.592113           1\n",
       "1       000001 1991-06-30  -1.821836           2\n",
       "2       000001 1991-07-31   0.807037           3\n",
       "3       000001 1991-08-31  23.378197           4\n",
       "4       000001 1991-09-30   3.358613           5\n",
       "...        ...        ...        ...         ...\n",
       "709878  605599 2023-08-31   1.040511          23\n",
       "709879  605599 2023-09-30   1.038189          24\n",
       "709880  605599 2023-10-31   1.052476          25\n",
       "709881  605599 2023-11-30   1.047631          26\n",
       "709882  605599 2023-12-31   1.017220          27\n",
       "\n",
       "[709883 rows x 4 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rolling_beta_per_stock(cross,window_months=60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>beta</th>\n",
       "      <th>data_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-05-31</td>\n",
       "      <td>1.592113</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-06-30</td>\n",
       "      <td>-1.821836</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-07-31</td>\n",
       "      <td>0.807037</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-08-31</td>\n",
       "      <td>23.378197</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1991-09-30</td>\n",
       "      <td>3.358613</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709878</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>1.040511</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709879</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>1.038189</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709880</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>1.052476</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709881</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>1.047631</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709882</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>1.017220</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>709883 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month       beta  data_count\n",
       "0       000001 1991-05-31   1.592113           1\n",
       "1       000001 1991-06-30  -1.821836           2\n",
       "2       000001 1991-07-31   0.807037           3\n",
       "3       000001 1991-08-31  23.378197           4\n",
       "4       000001 1991-09-30   3.358613           5\n",
       "...        ...        ...        ...         ...\n",
       "709878  605599 2023-08-31   1.040511          23\n",
       "709879  605599 2023-09-30   1.038189          24\n",
       "709880  605599 2023-10-31   1.052476          25\n",
       "709881  605599 2023-11-30   1.047631          26\n",
       "709882  605599 2023-12-31   1.017220          27\n",
       "\n",
       "[709883 rows x 4 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rolling_beta=pd.read_csv(\"D:/Pythonclasshomework/python-homework-gyt/datasets/rolling_betas.csv\")\n",
    "rolling_beta['month']=pd.to_datetime(rolling_beta['month'],format='%Y-%m-%d')\n",
    "rolling_beta['Stkcd']=rolling_beta['Stkcd'].apply(lambda x:'{:0>6}'.format(x))\n",
    "rolling_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "      <th>MKT</th>\n",
       "      <th>beta</th>\n",
       "      <th>data_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-04-30</td>\n",
       "      <td>38.50</td>\n",
       "      <td>13</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.599445e+09</td>\n",
       "      <td>5.185820e+09</td>\n",
       "      <td>21.678564</td>\n",
       "      <td>22.369194</td>\n",
       "      <td>0.477927</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.471835</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>1</td>\n",
       "      <td>0.356928</td>\n",
       "      <td>1.287898</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-05-31</td>\n",
       "      <td>45.20</td>\n",
       "      <td>14</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3.051816e+09</td>\n",
       "      <td>6.088287e+09</td>\n",
       "      <td>21.839003</td>\n",
       "      <td>22.529633</td>\n",
       "      <td>0.174026</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>-0.076888</td>\n",
       "      <td>1</td>\n",
       "      <td>0.698773</td>\n",
       "      <td>0.903993</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-06-30</td>\n",
       "      <td>42.00</td>\n",
       "      <td>15</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.835758e+09</td>\n",
       "      <td>5.657258e+09</td>\n",
       "      <td>21.765575</td>\n",
       "      <td>22.456205</td>\n",
       "      <td>-0.070796</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.076888</td>\n",
       "      <td>0.079622</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.112863</td>\n",
       "      <td>0.886484</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-07-31</td>\n",
       "      <td>45.60</td>\n",
       "      <td>16</td>\n",
       "      <td>23.0</td>\n",
       "      <td>3.078823e+09</td>\n",
       "      <td>6.142166e+09</td>\n",
       "      <td>21.847813</td>\n",
       "      <td>22.538443</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.079622</td>\n",
       "      <td>-0.101487</td>\n",
       "      <td>1</td>\n",
       "      <td>0.030265</td>\n",
       "      <td>0.878303</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-08-31</td>\n",
       "      <td>41.25</td>\n",
       "      <td>17</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2.785120e+09</td>\n",
       "      <td>5.556236e+09</td>\n",
       "      <td>21.747557</td>\n",
       "      <td>22.438187</td>\n",
       "      <td>-0.095395</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.101487</td>\n",
       "      <td>0.075120</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.152259</td>\n",
       "      <td>0.862723</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709878</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>13.56</td>\n",
       "      <td>24</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.955351e+09</td>\n",
       "      <td>1.054667e+10</td>\n",
       "      <td>22.797111</td>\n",
       "      <td>23.079076</td>\n",
       "      <td>-0.025862</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.056811</td>\n",
       "      <td>1.040511</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709879</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>14.54</td>\n",
       "      <td>25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.530295e+09</td>\n",
       "      <td>1.130889e+10</td>\n",
       "      <td>22.866890</td>\n",
       "      <td>23.148855</td>\n",
       "      <td>0.072271</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.006930</td>\n",
       "      <td>1.038189</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709880</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>13.85</td>\n",
       "      <td>26</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.125488e+09</td>\n",
       "      <td>1.077222e+10</td>\n",
       "      <td>22.818272</td>\n",
       "      <td>23.100237</td>\n",
       "      <td>-0.047455</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.027562</td>\n",
       "      <td>1.052476</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709881</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>13.48</td>\n",
       "      <td>27</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.908417e+09</td>\n",
       "      <td>1.048444e+10</td>\n",
       "      <td>22.791193</td>\n",
       "      <td>23.073159</td>\n",
       "      <td>-0.026715</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000549</td>\n",
       "      <td>1.047631</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>709882</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>14.89</td>\n",
       "      <td>28</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.735632e+09</td>\n",
       "      <td>1.158111e+10</td>\n",
       "      <td>22.890676</td>\n",
       "      <td>23.172641</td>\n",
       "      <td>0.104599</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.021244</td>\n",
       "      <td>1.017220</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>658182 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month  price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "11      000001 1992-04-30  38.50    13  22.0   2.599445e+09  5.185820e+09   \n",
       "12      000001 1992-05-31  45.20    14  21.0   3.051816e+09  6.088287e+09   \n",
       "13      000001 1992-06-30  42.00    15  22.0   2.835758e+09  5.657258e+09   \n",
       "14      000001 1992-07-31  45.60    16  23.0   3.078823e+09  6.142166e+09   \n",
       "15      000001 1992-08-31  41.25    17  21.0   2.785120e+09  5.556236e+09   \n",
       "...        ...        ...    ...   ...   ...            ...           ...   \n",
       "709878  605599 2023-08-31  13.56    24  23.0   7.955351e+09  1.054667e+10   \n",
       "709879  605599 2023-09-30  14.54    25  20.0   8.530295e+09  1.130889e+10   \n",
       "709880  605599 2023-10-31  13.85    26  17.0   8.125488e+09  1.077222e+10   \n",
       "709881  605599 2023-11-30  13.48    27  22.0   7.908417e+09  1.048444e+10   \n",
       "709882  605599 2023-12-31  14.89    28  21.0   8.735632e+09  1.158111e+10   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \\\n",
       "11      21.678564  22.369194  0.477927  0.006092  0.471835  0.167934  1   \n",
       "12      21.839003  22.529633  0.174026  0.006092  0.167934 -0.076888  1   \n",
       "13      21.765575  22.456205 -0.070796  0.006092 -0.076888  0.079622  1   \n",
       "14      21.847813  22.538443  0.085714  0.006092  0.079622 -0.101487  1   \n",
       "15      21.747557  22.438187 -0.095395  0.006092 -0.101487  0.075120  1   \n",
       "...           ...        ...       ...       ...       ...       ... ..   \n",
       "709878  22.797111  23.079076 -0.025862  0.001241 -0.027103  0.071030  1   \n",
       "709879  22.866890  23.148855  0.072271  0.001241  0.071030 -0.048696  1   \n",
       "709880  22.818272  23.100237 -0.047455  0.001241 -0.048696 -0.027956  1   \n",
       "709881  22.791193  23.073159 -0.026715  0.001241 -0.027956  0.103358  1   \n",
       "709882  22.890676  23.172641  0.104599  0.001241  0.103358       NaN  1   \n",
       "\n",
       "             MKT      beta  data_count  \n",
       "11      0.356928  1.287898          12  \n",
       "12      0.698773  0.903993          13  \n",
       "13     -0.112863  0.886484          14  \n",
       "14      0.030265  0.878303          15  \n",
       "15     -0.152259  0.862723          16  \n",
       "...          ...       ...         ...  \n",
       "709878 -0.056811  1.040511          23  \n",
       "709879 -0.006930  1.038189          24  \n",
       "709880 -0.027562  1.052476          25  \n",
       "709881  0.000549  1.047631          26  \n",
       "709882 -0.021244  1.017220          27  \n",
       "\n",
       "[658182 rows x 17 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cross_beta=pd.merge(cross,rolling_beta,on=(\"Stkcd\",'month'),how='left')\n",
    "cross_beta=cross_beta[cross_beta['data_count']>=12]\n",
    "cross_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>five</th>\n",
       "      <th>six</th>\n",
       "      <th>seven</th>\n",
       "      <th>eight</th>\n",
       "      <th>nine</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1991-12-31</th>\n",
       "      <td>0.071958</td>\n",
       "      <td>0.097514</td>\n",
       "      <td>0.099366</td>\n",
       "      <td>0.101218</td>\n",
       "      <td>0.149016</td>\n",
       "      <td>0.196814</td>\n",
       "      <td>0.221577</td>\n",
       "      <td>0.246341</td>\n",
       "      <td>0.253227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-01-31</th>\n",
       "      <td>0.082628</td>\n",
       "      <td>0.094319</td>\n",
       "      <td>0.097810</td>\n",
       "      <td>0.157148</td>\n",
       "      <td>0.221045</td>\n",
       "      <td>0.251824</td>\n",
       "      <td>0.263519</td>\n",
       "      <td>0.573773</td>\n",
       "      <td>1.928159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-02-29</th>\n",
       "      <td>0.081935</td>\n",
       "      <td>0.093897</td>\n",
       "      <td>0.101806</td>\n",
       "      <td>0.168023</td>\n",
       "      <td>0.228162</td>\n",
       "      <td>0.251692</td>\n",
       "      <td>0.263399</td>\n",
       "      <td>0.573994</td>\n",
       "      <td>1.927546</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-03-31</th>\n",
       "      <td>0.061369</td>\n",
       "      <td>0.078081</td>\n",
       "      <td>0.093707</td>\n",
       "      <td>0.157350</td>\n",
       "      <td>0.216032</td>\n",
       "      <td>0.240144</td>\n",
       "      <td>0.260510</td>\n",
       "      <td>0.586451</td>\n",
       "      <td>1.895887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-04-30</th>\n",
       "      <td>-0.065538</td>\n",
       "      <td>-0.007246</td>\n",
       "      <td>0.048498</td>\n",
       "      <td>0.063799</td>\n",
       "      <td>0.158559</td>\n",
       "      <td>0.263182</td>\n",
       "      <td>0.443521</td>\n",
       "      <td>1.287898</td>\n",
       "      <td>1.787884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-08-31</th>\n",
       "      <td>0.577038</td>\n",
       "      <td>0.746623</td>\n",
       "      <td>0.860449</td>\n",
       "      <td>0.956527</td>\n",
       "      <td>1.038594</td>\n",
       "      <td>1.132528</td>\n",
       "      <td>1.239638</td>\n",
       "      <td>1.380807</td>\n",
       "      <td>1.601730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-09-30</th>\n",
       "      <td>0.559448</td>\n",
       "      <td>0.731075</td>\n",
       "      <td>0.849778</td>\n",
       "      <td>0.942773</td>\n",
       "      <td>1.023284</td>\n",
       "      <td>1.124444</td>\n",
       "      <td>1.233064</td>\n",
       "      <td>1.381506</td>\n",
       "      <td>1.600269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-10-31</th>\n",
       "      <td>0.557654</td>\n",
       "      <td>0.731994</td>\n",
       "      <td>0.849838</td>\n",
       "      <td>0.943937</td>\n",
       "      <td>1.024999</td>\n",
       "      <td>1.121929</td>\n",
       "      <td>1.232482</td>\n",
       "      <td>1.381136</td>\n",
       "      <td>1.603640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-11-30</th>\n",
       "      <td>0.546811</td>\n",
       "      <td>0.720276</td>\n",
       "      <td>0.839969</td>\n",
       "      <td>0.934261</td>\n",
       "      <td>1.019290</td>\n",
       "      <td>1.115750</td>\n",
       "      <td>1.230708</td>\n",
       "      <td>1.384028</td>\n",
       "      <td>1.605226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>0.544689</td>\n",
       "      <td>0.721764</td>\n",
       "      <td>0.839339</td>\n",
       "      <td>0.932356</td>\n",
       "      <td>1.021292</td>\n",
       "      <td>1.115482</td>\n",
       "      <td>1.231099</td>\n",
       "      <td>1.383001</td>\n",
       "      <td>1.599392</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>385 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 one       two     three      four      five       six  \\\n",
       "month                                                                    \n",
       "1991-12-31  0.071958  0.097514  0.099366  0.101218  0.149016  0.196814   \n",
       "1992-01-31  0.082628  0.094319  0.097810  0.157148  0.221045  0.251824   \n",
       "1992-02-29  0.081935  0.093897  0.101806  0.168023  0.228162  0.251692   \n",
       "1992-03-31  0.061369  0.078081  0.093707  0.157350  0.216032  0.240144   \n",
       "1992-04-30 -0.065538 -0.007246  0.048498  0.063799  0.158559  0.263182   \n",
       "...              ...       ...       ...       ...       ...       ...   \n",
       "2023-08-31  0.577038  0.746623  0.860449  0.956527  1.038594  1.132528   \n",
       "2023-09-30  0.559448  0.731075  0.849778  0.942773  1.023284  1.124444   \n",
       "2023-10-31  0.557654  0.731994  0.849838  0.943937  1.024999  1.121929   \n",
       "2023-11-30  0.546811  0.720276  0.839969  0.934261  1.019290  1.115750   \n",
       "2023-12-31  0.544689  0.721764  0.839339  0.932356  1.021292  1.115482   \n",
       "\n",
       "               seven     eight      nine  \n",
       "month                                     \n",
       "1991-12-31  0.221577  0.246341  0.253227  \n",
       "1992-01-31  0.263519  0.573773  1.928159  \n",
       "1992-02-29  0.263399  0.573994  1.927546  \n",
       "1992-03-31  0.260510  0.586451  1.895887  \n",
       "1992-04-30  0.443521  1.287898  1.787884  \n",
       "...              ...       ...       ...  \n",
       "2023-08-31  1.239638  1.380807  1.601730  \n",
       "2023-09-30  1.233064  1.381506  1.600269  \n",
       "2023-10-31  1.232482  1.381136  1.603640  \n",
       "2023-11-30  1.230708  1.384028  1.605226  \n",
       "2023-12-31  1.231099  1.383001  1.599392  \n",
       "\n",
       "[385 rows x 9 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fenweishu = pd.DataFrame(\n",
    "    cross_beta.groupby(['month'])['beta'].quantile([0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9]))\n",
    "fenweishu = fenweishu.reset_index()\n",
    "fenweishu = fenweishu.pivot_table(index='month',columns='level_1',values='beta')\n",
    "fenweishu.columns = ['one','two','three','four','five','six','seven','eight','nine']\n",
    "fenweishu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "      <th>MKT</th>\n",
       "      <th>beta</th>\n",
       "      <th>data_count</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>five</th>\n",
       "      <th>six</th>\n",
       "      <th>seven</th>\n",
       "      <th>eight</th>\n",
       "      <th>nine</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-04-30</td>\n",
       "      <td>38.50</td>\n",
       "      <td>13</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.599445e+09</td>\n",
       "      <td>5.185820e+09</td>\n",
       "      <td>21.678564</td>\n",
       "      <td>22.369194</td>\n",
       "      <td>0.477927</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.471835</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>1</td>\n",
       "      <td>0.356928</td>\n",
       "      <td>1.287898</td>\n",
       "      <td>12</td>\n",
       "      <td>-0.065538</td>\n",
       "      <td>-0.007246</td>\n",
       "      <td>0.048498</td>\n",
       "      <td>0.063799</td>\n",
       "      <td>0.158559</td>\n",
       "      <td>0.263182</td>\n",
       "      <td>0.443521</td>\n",
       "      <td>1.287898</td>\n",
       "      <td>1.787884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-05-31</td>\n",
       "      <td>45.20</td>\n",
       "      <td>14</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3.051816e+09</td>\n",
       "      <td>6.088287e+09</td>\n",
       "      <td>21.839003</td>\n",
       "      <td>22.529633</td>\n",
       "      <td>0.174026</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>-0.076888</td>\n",
       "      <td>1</td>\n",
       "      <td>0.698773</td>\n",
       "      <td>0.903993</td>\n",
       "      <td>13</td>\n",
       "      <td>0.050273</td>\n",
       "      <td>0.126902</td>\n",
       "      <td>0.160976</td>\n",
       "      <td>0.190613</td>\n",
       "      <td>0.293657</td>\n",
       "      <td>0.505642</td>\n",
       "      <td>0.838514</td>\n",
       "      <td>0.903993</td>\n",
       "      <td>1.373230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-06-30</td>\n",
       "      <td>42.00</td>\n",
       "      <td>15</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.835758e+09</td>\n",
       "      <td>5.657258e+09</td>\n",
       "      <td>21.765575</td>\n",
       "      <td>22.456205</td>\n",
       "      <td>-0.070796</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.076888</td>\n",
       "      <td>0.079622</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.112863</td>\n",
       "      <td>0.886484</td>\n",
       "      <td>14</td>\n",
       "      <td>0.109463</td>\n",
       "      <td>0.186703</td>\n",
       "      <td>0.212601</td>\n",
       "      <td>0.220830</td>\n",
       "      <td>0.374333</td>\n",
       "      <td>0.738099</td>\n",
       "      <td>0.888928</td>\n",
       "      <td>1.064494</td>\n",
       "      <td>1.327165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-07-31</td>\n",
       "      <td>45.60</td>\n",
       "      <td>16</td>\n",
       "      <td>23.0</td>\n",
       "      <td>3.078823e+09</td>\n",
       "      <td>6.142166e+09</td>\n",
       "      <td>21.847813</td>\n",
       "      <td>22.538443</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.079622</td>\n",
       "      <td>-0.101487</td>\n",
       "      <td>1</td>\n",
       "      <td>0.030265</td>\n",
       "      <td>0.878303</td>\n",
       "      <td>15</td>\n",
       "      <td>0.116811</td>\n",
       "      <td>0.190736</td>\n",
       "      <td>0.217469</td>\n",
       "      <td>0.237481</td>\n",
       "      <td>0.525851</td>\n",
       "      <td>0.779806</td>\n",
       "      <td>0.878420</td>\n",
       "      <td>1.015655</td>\n",
       "      <td>1.295484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-08-31</td>\n",
       "      <td>41.25</td>\n",
       "      <td>17</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2.785120e+09</td>\n",
       "      <td>5.556236e+09</td>\n",
       "      <td>21.747557</td>\n",
       "      <td>22.438187</td>\n",
       "      <td>-0.095395</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.101487</td>\n",
       "      <td>0.075120</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.152259</td>\n",
       "      <td>0.862723</td>\n",
       "      <td>16</td>\n",
       "      <td>0.179535</td>\n",
       "      <td>0.277557</td>\n",
       "      <td>0.282518</td>\n",
       "      <td>0.398499</td>\n",
       "      <td>0.670156</td>\n",
       "      <td>0.828769</td>\n",
       "      <td>0.909351</td>\n",
       "      <td>1.059466</td>\n",
       "      <td>1.292737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658177</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>13.56</td>\n",
       "      <td>24</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.955351e+09</td>\n",
       "      <td>1.054667e+10</td>\n",
       "      <td>22.797111</td>\n",
       "      <td>23.079076</td>\n",
       "      <td>-0.025862</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.056811</td>\n",
       "      <td>1.040511</td>\n",
       "      <td>23</td>\n",
       "      <td>0.577038</td>\n",
       "      <td>0.746623</td>\n",
       "      <td>0.860449</td>\n",
       "      <td>0.956527</td>\n",
       "      <td>1.038594</td>\n",
       "      <td>1.132528</td>\n",
       "      <td>1.239638</td>\n",
       "      <td>1.380807</td>\n",
       "      <td>1.601730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658178</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>14.54</td>\n",
       "      <td>25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.530295e+09</td>\n",
       "      <td>1.130889e+10</td>\n",
       "      <td>22.866890</td>\n",
       "      <td>23.148855</td>\n",
       "      <td>0.072271</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.006930</td>\n",
       "      <td>1.038189</td>\n",
       "      <td>24</td>\n",
       "      <td>0.559448</td>\n",
       "      <td>0.731075</td>\n",
       "      <td>0.849778</td>\n",
       "      <td>0.942773</td>\n",
       "      <td>1.023284</td>\n",
       "      <td>1.124444</td>\n",
       "      <td>1.233064</td>\n",
       "      <td>1.381506</td>\n",
       "      <td>1.600269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658179</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>13.85</td>\n",
       "      <td>26</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.125488e+09</td>\n",
       "      <td>1.077222e+10</td>\n",
       "      <td>22.818272</td>\n",
       "      <td>23.100237</td>\n",
       "      <td>-0.047455</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.027562</td>\n",
       "      <td>1.052476</td>\n",
       "      <td>25</td>\n",
       "      <td>0.557654</td>\n",
       "      <td>0.731994</td>\n",
       "      <td>0.849838</td>\n",
       "      <td>0.943937</td>\n",
       "      <td>1.024999</td>\n",
       "      <td>1.121929</td>\n",
       "      <td>1.232482</td>\n",
       "      <td>1.381136</td>\n",
       "      <td>1.603640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658180</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>13.48</td>\n",
       "      <td>27</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.908417e+09</td>\n",
       "      <td>1.048444e+10</td>\n",
       "      <td>22.791193</td>\n",
       "      <td>23.073159</td>\n",
       "      <td>-0.026715</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000549</td>\n",
       "      <td>1.047631</td>\n",
       "      <td>26</td>\n",
       "      <td>0.546811</td>\n",
       "      <td>0.720276</td>\n",
       "      <td>0.839969</td>\n",
       "      <td>0.934261</td>\n",
       "      <td>1.019290</td>\n",
       "      <td>1.115750</td>\n",
       "      <td>1.230708</td>\n",
       "      <td>1.384028</td>\n",
       "      <td>1.605226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658181</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>14.89</td>\n",
       "      <td>28</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.735632e+09</td>\n",
       "      <td>1.158111e+10</td>\n",
       "      <td>22.890676</td>\n",
       "      <td>23.172641</td>\n",
       "      <td>0.104599</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.021244</td>\n",
       "      <td>1.017220</td>\n",
       "      <td>27</td>\n",
       "      <td>0.544689</td>\n",
       "      <td>0.721764</td>\n",
       "      <td>0.839339</td>\n",
       "      <td>0.932356</td>\n",
       "      <td>1.021292</td>\n",
       "      <td>1.115482</td>\n",
       "      <td>1.231099</td>\n",
       "      <td>1.383001</td>\n",
       "      <td>1.599392</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>658182 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month  price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "0       000001 1992-04-30  38.50    13  22.0   2.599445e+09  5.185820e+09   \n",
       "1       000001 1992-05-31  45.20    14  21.0   3.051816e+09  6.088287e+09   \n",
       "2       000001 1992-06-30  42.00    15  22.0   2.835758e+09  5.657258e+09   \n",
       "3       000001 1992-07-31  45.60    16  23.0   3.078823e+09  6.142166e+09   \n",
       "4       000001 1992-08-31  41.25    17  21.0   2.785120e+09  5.556236e+09   \n",
       "...        ...        ...    ...   ...   ...            ...           ...   \n",
       "658177  605599 2023-08-31  13.56    24  23.0   7.955351e+09  1.054667e+10   \n",
       "658178  605599 2023-09-30  14.54    25  20.0   8.530295e+09  1.130889e+10   \n",
       "658179  605599 2023-10-31  13.85    26  17.0   8.125488e+09  1.077222e+10   \n",
       "658180  605599 2023-11-30  13.48    27  22.0   7.908417e+09  1.048444e+10   \n",
       "658181  605599 2023-12-31  14.89    28  21.0   8.735632e+09  1.158111e+10   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \\\n",
       "0       21.678564  22.369194  0.477927  0.006092  0.471835  0.167934  1   \n",
       "1       21.839003  22.529633  0.174026  0.006092  0.167934 -0.076888  1   \n",
       "2       21.765575  22.456205 -0.070796  0.006092 -0.076888  0.079622  1   \n",
       "3       21.847813  22.538443  0.085714  0.006092  0.079622 -0.101487  1   \n",
       "4       21.747557  22.438187 -0.095395  0.006092 -0.101487  0.075120  1   \n",
       "...           ...        ...       ...       ...       ...       ... ..   \n",
       "658177  22.797111  23.079076 -0.025862  0.001241 -0.027103  0.071030  1   \n",
       "658178  22.866890  23.148855  0.072271  0.001241  0.071030 -0.048696  1   \n",
       "658179  22.818272  23.100237 -0.047455  0.001241 -0.048696 -0.027956  1   \n",
       "658180  22.791193  23.073159 -0.026715  0.001241 -0.027956  0.103358  1   \n",
       "658181  22.890676  23.172641  0.104599  0.001241  0.103358       NaN  1   \n",
       "\n",
       "             MKT      beta  data_count       one       two     three  \\\n",
       "0       0.356928  1.287898          12 -0.065538 -0.007246  0.048498   \n",
       "1       0.698773  0.903993          13  0.050273  0.126902  0.160976   \n",
       "2      -0.112863  0.886484          14  0.109463  0.186703  0.212601   \n",
       "3       0.030265  0.878303          15  0.116811  0.190736  0.217469   \n",
       "4      -0.152259  0.862723          16  0.179535  0.277557  0.282518   \n",
       "...          ...       ...         ...       ...       ...       ...   \n",
       "658177 -0.056811  1.040511          23  0.577038  0.746623  0.860449   \n",
       "658178 -0.006930  1.038189          24  0.559448  0.731075  0.849778   \n",
       "658179 -0.027562  1.052476          25  0.557654  0.731994  0.849838   \n",
       "658180  0.000549  1.047631          26  0.546811  0.720276  0.839969   \n",
       "658181 -0.021244  1.017220          27  0.544689  0.721764  0.839339   \n",
       "\n",
       "            four      five       six     seven     eight      nine  \n",
       "0       0.063799  0.158559  0.263182  0.443521  1.287898  1.787884  \n",
       "1       0.190613  0.293657  0.505642  0.838514  0.903993  1.373230  \n",
       "2       0.220830  0.374333  0.738099  0.888928  1.064494  1.327165  \n",
       "3       0.237481  0.525851  0.779806  0.878420  1.015655  1.295484  \n",
       "4       0.398499  0.670156  0.828769  0.909351  1.059466  1.292737  \n",
       "...          ...       ...       ...       ...       ...       ...  \n",
       "658177  0.956527  1.038594  1.132528  1.239638  1.380807  1.601730  \n",
       "658178  0.942773  1.023284  1.124444  1.233064  1.381506  1.600269  \n",
       "658179  0.943937  1.024999  1.121929  1.232482  1.381136  1.603640  \n",
       "658180  0.934261  1.019290  1.115750  1.230708  1.384028  1.605226  \n",
       "658181  0.932356  1.021292  1.115482  1.231099  1.383001  1.599392  \n",
       "\n",
       "[658182 rows x 26 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio = pd.merge(cross_beta,fenweishu,on='month')\n",
    "portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Stkcd</th>\n",
       "      <th>month</th>\n",
       "      <th>price</th>\n",
       "      <th>Rank</th>\n",
       "      <th>Freq</th>\n",
       "      <th>floatingvalue</th>\n",
       "      <th>totalvalue</th>\n",
       "      <th>sizef</th>\n",
       "      <th>sizet</th>\n",
       "      <th>Return</th>\n",
       "      <th>rfmonth</th>\n",
       "      <th>ret</th>\n",
       "      <th>next_ret</th>\n",
       "      <th>w</th>\n",
       "      <th>MKT</th>\n",
       "      <th>beta</th>\n",
       "      <th>data_count</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>three</th>\n",
       "      <th>four</th>\n",
       "      <th>five</th>\n",
       "      <th>six</th>\n",
       "      <th>seven</th>\n",
       "      <th>eight</th>\n",
       "      <th>nine</th>\n",
       "      <th>sort</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-04-30</td>\n",
       "      <td>38.50</td>\n",
       "      <td>13</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.599445e+09</td>\n",
       "      <td>5.185820e+09</td>\n",
       "      <td>21.678564</td>\n",
       "      <td>22.369194</td>\n",
       "      <td>0.477927</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.471835</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>1</td>\n",
       "      <td>0.356928</td>\n",
       "      <td>1.287898</td>\n",
       "      <td>12</td>\n",
       "      <td>-0.065538</td>\n",
       "      <td>-0.007246</td>\n",
       "      <td>0.048498</td>\n",
       "      <td>0.063799</td>\n",
       "      <td>0.158559</td>\n",
       "      <td>0.263182</td>\n",
       "      <td>0.443521</td>\n",
       "      <td>1.287898</td>\n",
       "      <td>1.787884</td>\n",
       "      <td>P8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-05-31</td>\n",
       "      <td>45.20</td>\n",
       "      <td>14</td>\n",
       "      <td>21.0</td>\n",
       "      <td>3.051816e+09</td>\n",
       "      <td>6.088287e+09</td>\n",
       "      <td>21.839003</td>\n",
       "      <td>22.529633</td>\n",
       "      <td>0.174026</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>-0.076888</td>\n",
       "      <td>1</td>\n",
       "      <td>0.698773</td>\n",
       "      <td>0.903993</td>\n",
       "      <td>13</td>\n",
       "      <td>0.050273</td>\n",
       "      <td>0.126902</td>\n",
       "      <td>0.160976</td>\n",
       "      <td>0.190613</td>\n",
       "      <td>0.293657</td>\n",
       "      <td>0.505642</td>\n",
       "      <td>0.838514</td>\n",
       "      <td>0.903993</td>\n",
       "      <td>1.373230</td>\n",
       "      <td>P8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-06-30</td>\n",
       "      <td>42.00</td>\n",
       "      <td>15</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.835758e+09</td>\n",
       "      <td>5.657258e+09</td>\n",
       "      <td>21.765575</td>\n",
       "      <td>22.456205</td>\n",
       "      <td>-0.070796</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.076888</td>\n",
       "      <td>0.079622</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.112863</td>\n",
       "      <td>0.886484</td>\n",
       "      <td>14</td>\n",
       "      <td>0.109463</td>\n",
       "      <td>0.186703</td>\n",
       "      <td>0.212601</td>\n",
       "      <td>0.220830</td>\n",
       "      <td>0.374333</td>\n",
       "      <td>0.738099</td>\n",
       "      <td>0.888928</td>\n",
       "      <td>1.064494</td>\n",
       "      <td>1.327165</td>\n",
       "      <td>P7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-07-31</td>\n",
       "      <td>45.60</td>\n",
       "      <td>16</td>\n",
       "      <td>23.0</td>\n",
       "      <td>3.078823e+09</td>\n",
       "      <td>6.142166e+09</td>\n",
       "      <td>21.847813</td>\n",
       "      <td>22.538443</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>0.079622</td>\n",
       "      <td>-0.101487</td>\n",
       "      <td>1</td>\n",
       "      <td>0.030265</td>\n",
       "      <td>0.878303</td>\n",
       "      <td>15</td>\n",
       "      <td>0.116811</td>\n",
       "      <td>0.190736</td>\n",
       "      <td>0.217469</td>\n",
       "      <td>0.237481</td>\n",
       "      <td>0.525851</td>\n",
       "      <td>0.779806</td>\n",
       "      <td>0.878420</td>\n",
       "      <td>1.015655</td>\n",
       "      <td>1.295484</td>\n",
       "      <td>P7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000001</td>\n",
       "      <td>1992-08-31</td>\n",
       "      <td>41.25</td>\n",
       "      <td>17</td>\n",
       "      <td>21.0</td>\n",
       "      <td>2.785120e+09</td>\n",
       "      <td>5.556236e+09</td>\n",
       "      <td>21.747557</td>\n",
       "      <td>22.438187</td>\n",
       "      <td>-0.095395</td>\n",
       "      <td>0.006092</td>\n",
       "      <td>-0.101487</td>\n",
       "      <td>0.075120</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.152259</td>\n",
       "      <td>0.862723</td>\n",
       "      <td>16</td>\n",
       "      <td>0.179535</td>\n",
       "      <td>0.277557</td>\n",
       "      <td>0.282518</td>\n",
       "      <td>0.398499</td>\n",
       "      <td>0.670156</td>\n",
       "      <td>0.828769</td>\n",
       "      <td>0.909351</td>\n",
       "      <td>1.059466</td>\n",
       "      <td>1.292737</td>\n",
       "      <td>P7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658176</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-07-31</td>\n",
       "      <td>13.92</td>\n",
       "      <td>23</td>\n",
       "      <td>21.0</td>\n",
       "      <td>8.166555e+09</td>\n",
       "      <td>1.082667e+10</td>\n",
       "      <td>22.823313</td>\n",
       "      <td>23.105278</td>\n",
       "      <td>0.053747</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.052506</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>1</td>\n",
       "      <td>0.035528</td>\n",
       "      <td>1.059668</td>\n",
       "      <td>22</td>\n",
       "      <td>0.587592</td>\n",
       "      <td>0.755658</td>\n",
       "      <td>0.866035</td>\n",
       "      <td>0.962244</td>\n",
       "      <td>1.050305</td>\n",
       "      <td>1.140063</td>\n",
       "      <td>1.252768</td>\n",
       "      <td>1.391693</td>\n",
       "      <td>1.614129</td>\n",
       "      <td>P6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658177</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>13.56</td>\n",
       "      <td>24</td>\n",
       "      <td>23.0</td>\n",
       "      <td>7.955351e+09</td>\n",
       "      <td>1.054667e+10</td>\n",
       "      <td>22.797111</td>\n",
       "      <td>23.079076</td>\n",
       "      <td>-0.025862</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027103</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.056811</td>\n",
       "      <td>1.040511</td>\n",
       "      <td>23</td>\n",
       "      <td>0.577038</td>\n",
       "      <td>0.746623</td>\n",
       "      <td>0.860449</td>\n",
       "      <td>0.956527</td>\n",
       "      <td>1.038594</td>\n",
       "      <td>1.132528</td>\n",
       "      <td>1.239638</td>\n",
       "      <td>1.380807</td>\n",
       "      <td>1.601730</td>\n",
       "      <td>P6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658178</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>14.54</td>\n",
       "      <td>25</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.530295e+09</td>\n",
       "      <td>1.130889e+10</td>\n",
       "      <td>22.866890</td>\n",
       "      <td>23.148855</td>\n",
       "      <td>0.072271</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>0.071030</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.006930</td>\n",
       "      <td>1.038189</td>\n",
       "      <td>24</td>\n",
       "      <td>0.559448</td>\n",
       "      <td>0.731075</td>\n",
       "      <td>0.849778</td>\n",
       "      <td>0.942773</td>\n",
       "      <td>1.023284</td>\n",
       "      <td>1.124444</td>\n",
       "      <td>1.233064</td>\n",
       "      <td>1.381506</td>\n",
       "      <td>1.600269</td>\n",
       "      <td>P6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658179</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>13.85</td>\n",
       "      <td>26</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.125488e+09</td>\n",
       "      <td>1.077222e+10</td>\n",
       "      <td>22.818272</td>\n",
       "      <td>23.100237</td>\n",
       "      <td>-0.047455</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.048696</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>1</td>\n",
       "      <td>-0.027562</td>\n",
       "      <td>1.052476</td>\n",
       "      <td>25</td>\n",
       "      <td>0.557654</td>\n",
       "      <td>0.731994</td>\n",
       "      <td>0.849838</td>\n",
       "      <td>0.943937</td>\n",
       "      <td>1.024999</td>\n",
       "      <td>1.121929</td>\n",
       "      <td>1.232482</td>\n",
       "      <td>1.381136</td>\n",
       "      <td>1.603640</td>\n",
       "      <td>P6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658180</th>\n",
       "      <td>605599</td>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>13.48</td>\n",
       "      <td>27</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.908417e+09</td>\n",
       "      <td>1.048444e+10</td>\n",
       "      <td>22.791193</td>\n",
       "      <td>23.073159</td>\n",
       "      <td>-0.026715</td>\n",
       "      <td>0.001241</td>\n",
       "      <td>-0.027956</td>\n",
       "      <td>0.103358</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000549</td>\n",
       "      <td>1.047631</td>\n",
       "      <td>26</td>\n",
       "      <td>0.546811</td>\n",
       "      <td>0.720276</td>\n",
       "      <td>0.839969</td>\n",
       "      <td>0.934261</td>\n",
       "      <td>1.019290</td>\n",
       "      <td>1.115750</td>\n",
       "      <td>1.230708</td>\n",
       "      <td>1.384028</td>\n",
       "      <td>1.605226</td>\n",
       "      <td>P6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>649364 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Stkcd      month  price  Rank  Freq  floatingvalue    totalvalue  \\\n",
       "0       000001 1992-04-30  38.50    13  22.0   2.599445e+09  5.185820e+09   \n",
       "1       000001 1992-05-31  45.20    14  21.0   3.051816e+09  6.088287e+09   \n",
       "2       000001 1992-06-30  42.00    15  22.0   2.835758e+09  5.657258e+09   \n",
       "3       000001 1992-07-31  45.60    16  23.0   3.078823e+09  6.142166e+09   \n",
       "4       000001 1992-08-31  41.25    17  21.0   2.785120e+09  5.556236e+09   \n",
       "...        ...        ...    ...   ...   ...            ...           ...   \n",
       "658176  605599 2023-07-31  13.92    23  21.0   8.166555e+09  1.082667e+10   \n",
       "658177  605599 2023-08-31  13.56    24  23.0   7.955351e+09  1.054667e+10   \n",
       "658178  605599 2023-09-30  14.54    25  20.0   8.530295e+09  1.130889e+10   \n",
       "658179  605599 2023-10-31  13.85    26  17.0   8.125488e+09  1.077222e+10   \n",
       "658180  605599 2023-11-30  13.48    27  22.0   7.908417e+09  1.048444e+10   \n",
       "\n",
       "            sizef      sizet    Return   rfmonth       ret  next_ret  w  \\\n",
       "0       21.678564  22.369194  0.477927  0.006092  0.471835  0.167934  1   \n",
       "1       21.839003  22.529633  0.174026  0.006092  0.167934 -0.076888  1   \n",
       "2       21.765575  22.456205 -0.070796  0.006092 -0.076888  0.079622  1   \n",
       "3       21.847813  22.538443  0.085714  0.006092  0.079622 -0.101487  1   \n",
       "4       21.747557  22.438187 -0.095395  0.006092 -0.101487  0.075120  1   \n",
       "...           ...        ...       ...       ...       ...       ... ..   \n",
       "658176  22.823313  23.105278  0.053747  0.001241  0.052506 -0.027103  1   \n",
       "658177  22.797111  23.079076 -0.025862  0.001241 -0.027103  0.071030  1   \n",
       "658178  22.866890  23.148855  0.072271  0.001241  0.071030 -0.048696  1   \n",
       "658179  22.818272  23.100237 -0.047455  0.001241 -0.048696 -0.027956  1   \n",
       "658180  22.791193  23.073159 -0.026715  0.001241 -0.027956  0.103358  1   \n",
       "\n",
       "             MKT      beta  data_count       one       two     three  \\\n",
       "0       0.356928  1.287898          12 -0.065538 -0.007246  0.048498   \n",
       "1       0.698773  0.903993          13  0.050273  0.126902  0.160976   \n",
       "2      -0.112863  0.886484          14  0.109463  0.186703  0.212601   \n",
       "3       0.030265  0.878303          15  0.116811  0.190736  0.217469   \n",
       "4      -0.152259  0.862723          16  0.179535  0.277557  0.282518   \n",
       "...          ...       ...         ...       ...       ...       ...   \n",
       "658176  0.035528  1.059668          22  0.587592  0.755658  0.866035   \n",
       "658177 -0.056811  1.040511          23  0.577038  0.746623  0.860449   \n",
       "658178 -0.006930  1.038189          24  0.559448  0.731075  0.849778   \n",
       "658179 -0.027562  1.052476          25  0.557654  0.731994  0.849838   \n",
       "658180  0.000549  1.047631          26  0.546811  0.720276  0.839969   \n",
       "\n",
       "            four      five       six     seven     eight      nine sort  \n",
       "0       0.063799  0.158559  0.263182  0.443521  1.287898  1.787884   P8  \n",
       "1       0.190613  0.293657  0.505642  0.838514  0.903993  1.373230   P8  \n",
       "2       0.220830  0.374333  0.738099  0.888928  1.064494  1.327165   P7  \n",
       "3       0.237481  0.525851  0.779806  0.878420  1.015655  1.295484   P7  \n",
       "4       0.398499  0.670156  0.828769  0.909351  1.059466  1.292737   P7  \n",
       "...          ...       ...       ...       ...       ...       ...  ...  \n",
       "658176  0.962244  1.050305  1.140063  1.252768  1.391693  1.614129   P6  \n",
       "658177  0.956527  1.038594  1.132528  1.239638  1.380807  1.601730   P6  \n",
       "658178  0.942773  1.023284  1.124444  1.233064  1.381506  1.600269   P6  \n",
       "658179  0.943937  1.024999  1.121929  1.232482  1.381136  1.603640   P6  \n",
       "658180  0.934261  1.019290  1.115750  1.230708  1.384028  1.605226   P6  \n",
       "\n",
       "[649364 rows x 27 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio['sort'] = np.where(\n",
    "    portfolio['beta'] <= portfolio['one'], 'P1',\n",
    "    np.where(\n",
    "        portfolio['beta'] <= portfolio['two'], 'P2',\n",
    "        np.where(\n",
    "            portfolio['beta'] <= portfolio['three'], 'P3',\n",
    "            np.where(\n",
    "                portfolio['beta'] <= portfolio['four'], 'P4',\n",
    "                np.where(\n",
    "                    portfolio['beta'] <= portfolio['five'], 'P5',\n",
    "                    np.where(\n",
    "                        portfolio['beta'] <= portfolio['six'], 'P6',\n",
    "                        np.where(\n",
    "                            portfolio['beta'] <= portfolio['seven'], 'P7',\n",
    "                            np.where(\n",
    "                                portfolio['beta'] <= portfolio['eight'], 'P8',\n",
    "                                np.where(\n",
    "                                    portfolio['beta'] <= portfolio['nine'],\n",
    "                                    'P9', 'Pmax')))))))))\n",
    "portfolio = portfolio.dropna(subset=['floatingvalue','next_ret','beta'])\n",
    "portfolio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th>sort</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">1991-12-31</th>\n",
       "      <th>P1</th>\n",
       "      <td>0.143309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P2</th>\n",
       "      <td>0.144150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P4</th>\n",
       "      <td>0.155164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P6</th>\n",
       "      <td>0.066497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P8</th>\n",
       "      <td>0.087397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2023-11-30</th>\n",
       "      <th>P6</th>\n",
       "      <td>-0.024932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P7</th>\n",
       "      <td>-0.027459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P8</th>\n",
       "      <td>-0.024982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>P9</th>\n",
       "      <td>-0.026196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pmax</th>\n",
       "      <td>-0.027796</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3835 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        0\n",
       "month      sort          \n",
       "1991-12-31 P1    0.143309\n",
       "           P2    0.144150\n",
       "           P4    0.155164\n",
       "           P6    0.066497\n",
       "           P8    0.087397\n",
       "...                   ...\n",
       "2023-11-30 P6   -0.024932\n",
       "           P7   -0.027459\n",
       "           P8   -0.024982\n",
       "           P9   -0.026196\n",
       "           Pmax -0.027796\n",
       "\n",
       "[3835 rows x 1 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio_beta =  pd.DataFrame(\n",
    "    portfolio.groupby(['month','sort']).apply(lambda x: np.average(x['next_ret'],weights = x['floatingvalue']),include_groups=False))\n",
    "portfolio_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>sort</th>\n",
       "      <th>p</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1992-01-31</td>\n",
       "      <td>P1</td>\n",
       "      <td>0.143309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1992-01-31</td>\n",
       "      <td>P2</td>\n",
       "      <td>0.144150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1992-01-31</td>\n",
       "      <td>P4</td>\n",
       "      <td>0.155164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1992-01-31</td>\n",
       "      <td>P6</td>\n",
       "      <td>0.066497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1992-01-31</td>\n",
       "      <td>P8</td>\n",
       "      <td>0.087397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3830</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>P6</td>\n",
       "      <td>-0.024932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3831</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>P7</td>\n",
       "      <td>-0.027459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3832</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>P8</td>\n",
       "      <td>-0.024982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3833</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>P9</td>\n",
       "      <td>-0.026196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3834</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>Pmax</td>\n",
       "      <td>-0.027796</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3835 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          month  sort         p\n",
       "0    1992-01-31    P1  0.143309\n",
       "1    1992-01-31    P2  0.144150\n",
       "2    1992-01-31    P4  0.155164\n",
       "3    1992-01-31    P6  0.066497\n",
       "4    1992-01-31    P8  0.087397\n",
       "...         ...   ...       ...\n",
       "3830 2023-12-31    P6 -0.024932\n",
       "3831 2023-12-31    P7 -0.027459\n",
       "3832 2023-12-31    P8 -0.024982\n",
       "3833 2023-12-31    P9 -0.026196\n",
       "3834 2023-12-31  Pmax -0.027796\n",
       "\n",
       "[3835 rows x 3 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio_beta = portfolio_beta.reset_index()\n",
    "portfolio_beta.columns = ['month', 'sort', 'p']\n",
    "portfolio_beta['month'] = portfolio_beta['month'] + MonthEnd(1)\n",
    "portfolio_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sort</th>\n",
       "      <th>P1</th>\n",
       "      <th>P2</th>\n",
       "      <th>P3</th>\n",
       "      <th>P4</th>\n",
       "      <th>P5</th>\n",
       "      <th>P6</th>\n",
       "      <th>P7</th>\n",
       "      <th>P8</th>\n",
       "      <th>P9</th>\n",
       "      <th>Pmax</th>\n",
       "      <th>My_portfolio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1992-01-31</th>\n",
       "      <td>0.143309</td>\n",
       "      <td>0.144150</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.155164</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.066497</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.087397</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.024295</td>\n",
       "      <td>0.119014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-02-29</th>\n",
       "      <td>0.104298</td>\n",
       "      <td>0.016538</td>\n",
       "      <td>0.067644</td>\n",
       "      <td>0.853761</td>\n",
       "      <td>1.923706</td>\n",
       "      <td>0.081949</td>\n",
       "      <td>0.003954</td>\n",
       "      <td>0.017056</td>\n",
       "      <td>-0.047933</td>\n",
       "      <td>-0.055072</td>\n",
       "      <td>0.159370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-03-31</th>\n",
       "      <td>0.136385</td>\n",
       "      <td>0.014243</td>\n",
       "      <td>0.044922</td>\n",
       "      <td>0.122783</td>\n",
       "      <td>0.139400</td>\n",
       "      <td>0.055180</td>\n",
       "      <td>0.003962</td>\n",
       "      <td>0.108161</td>\n",
       "      <td>0.103078</td>\n",
       "      <td>0.090903</td>\n",
       "      <td>0.045482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-04-30</th>\n",
       "      <td>-0.155729</td>\n",
       "      <td>0.137519</td>\n",
       "      <td>0.049913</td>\n",
       "      <td>-0.089302</td>\n",
       "      <td>-0.390592</td>\n",
       "      <td>-0.036502</td>\n",
       "      <td>0.599388</td>\n",
       "      <td>-0.016244</td>\n",
       "      <td>0.647451</td>\n",
       "      <td>0.594847</td>\n",
       "      <td>-0.750576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1992-05-31</th>\n",
       "      <td>0.337114</td>\n",
       "      <td>0.365337</td>\n",
       "      <td>0.582005</td>\n",
       "      <td>1.582324</td>\n",
       "      <td>0.175726</td>\n",
       "      <td>-0.000964</td>\n",
       "      <td>0.473241</td>\n",
       "      <td>0.167934</td>\n",
       "      <td>0.445337</td>\n",
       "      <td>0.489509</td>\n",
       "      <td>-0.152395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-08-31</th>\n",
       "      <td>-0.036412</td>\n",
       "      <td>-0.054473</td>\n",
       "      <td>-0.068013</td>\n",
       "      <td>-0.057719</td>\n",
       "      <td>-0.068990</td>\n",
       "      <td>-0.058171</td>\n",
       "      <td>-0.054705</td>\n",
       "      <td>-0.059133</td>\n",
       "      <td>-0.063060</td>\n",
       "      <td>-0.071705</td>\n",
       "      <td>0.035293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-09-30</th>\n",
       "      <td>0.018065</td>\n",
       "      <td>0.006200</td>\n",
       "      <td>-0.007416</td>\n",
       "      <td>-0.010419</td>\n",
       "      <td>-0.004966</td>\n",
       "      <td>-0.001680</td>\n",
       "      <td>-0.017780</td>\n",
       "      <td>-0.035491</td>\n",
       "      <td>-0.016884</td>\n",
       "      <td>-0.018909</td>\n",
       "      <td>0.036975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-10-31</th>\n",
       "      <td>-0.026936</td>\n",
       "      <td>-0.035263</td>\n",
       "      <td>-0.035169</td>\n",
       "      <td>-0.045496</td>\n",
       "      <td>-0.020575</td>\n",
       "      <td>-0.030723</td>\n",
       "      <td>-0.040041</td>\n",
       "      <td>-0.039563</td>\n",
       "      <td>-0.019571</td>\n",
       "      <td>0.002289</td>\n",
       "      <td>-0.029226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-11-30</th>\n",
       "      <td>0.011564</td>\n",
       "      <td>0.005363</td>\n",
       "      <td>0.015737</td>\n",
       "      <td>0.014640</td>\n",
       "      <td>-0.001344</td>\n",
       "      <td>-0.022379</td>\n",
       "      <td>0.027644</td>\n",
       "      <td>-0.017196</td>\n",
       "      <td>-0.017168</td>\n",
       "      <td>-0.010074</td>\n",
       "      <td>0.021638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>-0.006525</td>\n",
       "      <td>-0.023350</td>\n",
       "      <td>-0.015199</td>\n",
       "      <td>-0.026276</td>\n",
       "      <td>-0.024242</td>\n",
       "      <td>-0.024932</td>\n",
       "      <td>-0.027459</td>\n",
       "      <td>-0.024982</td>\n",
       "      <td>-0.026196</td>\n",
       "      <td>-0.027796</td>\n",
       "      <td>0.021271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>384 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "sort              P1        P2        P3        P4        P5        P6  \\\n",
       "month                                                                    \n",
       "1992-01-31  0.143309  0.144150       NaN  0.155164       NaN  0.066497   \n",
       "1992-02-29  0.104298  0.016538  0.067644  0.853761  1.923706  0.081949   \n",
       "1992-03-31  0.136385  0.014243  0.044922  0.122783  0.139400  0.055180   \n",
       "1992-04-30 -0.155729  0.137519  0.049913 -0.089302 -0.390592 -0.036502   \n",
       "1992-05-31  0.337114  0.365337  0.582005  1.582324  0.175726 -0.000964   \n",
       "...              ...       ...       ...       ...       ...       ...   \n",
       "2023-08-31 -0.036412 -0.054473 -0.068013 -0.057719 -0.068990 -0.058171   \n",
       "2023-09-30  0.018065  0.006200 -0.007416 -0.010419 -0.004966 -0.001680   \n",
       "2023-10-31 -0.026936 -0.035263 -0.035169 -0.045496 -0.020575 -0.030723   \n",
       "2023-11-30  0.011564  0.005363  0.015737  0.014640 -0.001344 -0.022379   \n",
       "2023-12-31 -0.006525 -0.023350 -0.015199 -0.026276 -0.024242 -0.024932   \n",
       "\n",
       "sort              P7        P8        P9      Pmax  My_portfolio  \n",
       "month                                                             \n",
       "1992-01-31       NaN  0.087397       NaN  0.024295      0.119014  \n",
       "1992-02-29  0.003954  0.017056 -0.047933 -0.055072      0.159370  \n",
       "1992-03-31  0.003962  0.108161  0.103078  0.090903      0.045482  \n",
       "1992-04-30  0.599388 -0.016244  0.647451  0.594847     -0.750576  \n",
       "1992-05-31  0.473241  0.167934  0.445337  0.489509     -0.152395  \n",
       "...              ...       ...       ...       ...           ...  \n",
       "2023-08-31 -0.054705 -0.059133 -0.063060 -0.071705      0.035293  \n",
       "2023-09-30 -0.017780 -0.035491 -0.016884 -0.018909      0.036975  \n",
       "2023-10-31 -0.040041 -0.039563 -0.019571  0.002289     -0.029226  \n",
       "2023-11-30  0.027644 -0.017196 -0.017168 -0.010074      0.021638  \n",
       "2023-12-31 -0.027459 -0.024982 -0.026196 -0.027796      0.021271  \n",
       "\n",
       "[384 rows x 11 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio_beta = portfolio_beta.pivot_table(index='month',\n",
    "                                            columns='sort',\n",
    "                                            values='p')\n",
    "portfolio_beta['My_portfolio'] = portfolio_beta['P1'] - portfolio_beta['Pmax']\n",
    "portfolio_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>sort</th>\n",
       "      <th>P1</th>\n",
       "      <th>P2</th>\n",
       "      <th>P3</th>\n",
       "      <th>P4</th>\n",
       "      <th>P5</th>\n",
       "      <th>P6</th>\n",
       "      <th>P7</th>\n",
       "      <th>P8</th>\n",
       "      <th>P9</th>\n",
       "      <th>Pmax</th>\n",
       "      <th>My_portfolio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1995-01-31</th>\n",
       "      <td>-0.119180</td>\n",
       "      <td>-0.072110</td>\n",
       "      <td>-0.130556</td>\n",
       "      <td>-0.124243</td>\n",
       "      <td>-0.120475</td>\n",
       "      <td>-0.126131</td>\n",
       "      <td>-0.135343</td>\n",
       "      <td>-0.145437</td>\n",
       "      <td>-0.152750</td>\n",
       "      <td>-0.155980</td>\n",
       "      <td>0.036800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-02-28</th>\n",
       "      <td>-0.009711</td>\n",
       "      <td>-0.008694</td>\n",
       "      <td>-0.010877</td>\n",
       "      <td>-0.012167</td>\n",
       "      <td>-0.006294</td>\n",
       "      <td>-0.027947</td>\n",
       "      <td>-0.032387</td>\n",
       "      <td>-0.039242</td>\n",
       "      <td>-0.041955</td>\n",
       "      <td>-0.051491</td>\n",
       "      <td>0.041780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-03-31</th>\n",
       "      <td>0.062154</td>\n",
       "      <td>0.058339</td>\n",
       "      <td>0.075692</td>\n",
       "      <td>0.104134</td>\n",
       "      <td>0.089152</td>\n",
       "      <td>0.138383</td>\n",
       "      <td>0.118963</td>\n",
       "      <td>0.147348</td>\n",
       "      <td>0.184887</td>\n",
       "      <td>0.218689</td>\n",
       "      <td>-0.156534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-04-30</th>\n",
       "      <td>-0.129923</td>\n",
       "      <td>-0.109784</td>\n",
       "      <td>-0.122973</td>\n",
       "      <td>-0.096805</td>\n",
       "      <td>-0.116945</td>\n",
       "      <td>-0.130956</td>\n",
       "      <td>-0.096548</td>\n",
       "      <td>-0.094319</td>\n",
       "      <td>-0.111986</td>\n",
       "      <td>-0.121447</td>\n",
       "      <td>-0.008475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995-05-31</th>\n",
       "      <td>0.078637</td>\n",
       "      <td>0.066627</td>\n",
       "      <td>0.099883</td>\n",
       "      <td>0.103732</td>\n",
       "      <td>0.156507</td>\n",
       "      <td>0.220783</td>\n",
       "      <td>0.199680</td>\n",
       "      <td>0.197807</td>\n",
       "      <td>0.245065</td>\n",
       "      <td>0.223204</td>\n",
       "      <td>-0.144567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-08-31</th>\n",
       "      <td>-0.036412</td>\n",
       "      <td>-0.054473</td>\n",
       "      <td>-0.068013</td>\n",
       "      <td>-0.057719</td>\n",
       "      <td>-0.068990</td>\n",
       "      <td>-0.058171</td>\n",
       "      <td>-0.054705</td>\n",
       "      <td>-0.059133</td>\n",
       "      <td>-0.063060</td>\n",
       "      <td>-0.071705</td>\n",
       "      <td>0.035293</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-09-30</th>\n",
       "      <td>0.018065</td>\n",
       "      <td>0.006200</td>\n",
       "      <td>-0.007416</td>\n",
       "      <td>-0.010419</td>\n",
       "      <td>-0.004966</td>\n",
       "      <td>-0.001680</td>\n",
       "      <td>-0.017780</td>\n",
       "      <td>-0.035491</td>\n",
       "      <td>-0.016884</td>\n",
       "      <td>-0.018909</td>\n",
       "      <td>0.036975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-10-31</th>\n",
       "      <td>-0.026936</td>\n",
       "      <td>-0.035263</td>\n",
       "      <td>-0.035169</td>\n",
       "      <td>-0.045496</td>\n",
       "      <td>-0.020575</td>\n",
       "      <td>-0.030723</td>\n",
       "      <td>-0.040041</td>\n",
       "      <td>-0.039563</td>\n",
       "      <td>-0.019571</td>\n",
       "      <td>0.002289</td>\n",
       "      <td>-0.029226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-11-30</th>\n",
       "      <td>0.011564</td>\n",
       "      <td>0.005363</td>\n",
       "      <td>0.015737</td>\n",
       "      <td>0.014640</td>\n",
       "      <td>-0.001344</td>\n",
       "      <td>-0.022379</td>\n",
       "      <td>0.027644</td>\n",
       "      <td>-0.017196</td>\n",
       "      <td>-0.017168</td>\n",
       "      <td>-0.010074</td>\n",
       "      <td>0.021638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>-0.006525</td>\n",
       "      <td>-0.023350</td>\n",
       "      <td>-0.015199</td>\n",
       "      <td>-0.026276</td>\n",
       "      <td>-0.024242</td>\n",
       "      <td>-0.024932</td>\n",
       "      <td>-0.027459</td>\n",
       "      <td>-0.024982</td>\n",
       "      <td>-0.026196</td>\n",
       "      <td>-0.027796</td>\n",
       "      <td>0.021271</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>348 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "sort              P1        P2        P3        P4        P5        P6  \\\n",
       "month                                                                    \n",
       "1995-01-31 -0.119180 -0.072110 -0.130556 -0.124243 -0.120475 -0.126131   \n",
       "1995-02-28 -0.009711 -0.008694 -0.010877 -0.012167 -0.006294 -0.027947   \n",
       "1995-03-31  0.062154  0.058339  0.075692  0.104134  0.089152  0.138383   \n",
       "1995-04-30 -0.129923 -0.109784 -0.122973 -0.096805 -0.116945 -0.130956   \n",
       "1995-05-31  0.078637  0.066627  0.099883  0.103732  0.156507  0.220783   \n",
       "...              ...       ...       ...       ...       ...       ...   \n",
       "2023-08-31 -0.036412 -0.054473 -0.068013 -0.057719 -0.068990 -0.058171   \n",
       "2023-09-30  0.018065  0.006200 -0.007416 -0.010419 -0.004966 -0.001680   \n",
       "2023-10-31 -0.026936 -0.035263 -0.035169 -0.045496 -0.020575 -0.030723   \n",
       "2023-11-30  0.011564  0.005363  0.015737  0.014640 -0.001344 -0.022379   \n",
       "2023-12-31 -0.006525 -0.023350 -0.015199 -0.026276 -0.024242 -0.024932   \n",
       "\n",
       "sort              P7        P8        P9      Pmax  My_portfolio  \n",
       "month                                                             \n",
       "1995-01-31 -0.135343 -0.145437 -0.152750 -0.155980      0.036800  \n",
       "1995-02-28 -0.032387 -0.039242 -0.041955 -0.051491      0.041780  \n",
       "1995-03-31  0.118963  0.147348  0.184887  0.218689     -0.156534  \n",
       "1995-04-30 -0.096548 -0.094319 -0.111986 -0.121447     -0.008475  \n",
       "1995-05-31  0.199680  0.197807  0.245065  0.223204     -0.144567  \n",
       "...              ...       ...       ...       ...           ...  \n",
       "2023-08-31 -0.054705 -0.059133 -0.063060 -0.071705      0.035293  \n",
       "2023-09-30 -0.017780 -0.035491 -0.016884 -0.018909      0.036975  \n",
       "2023-10-31 -0.040041 -0.039563 -0.019571  0.002289     -0.029226  \n",
       "2023-11-30  0.027644 -0.017196 -0.017168 -0.010074      0.021638  \n",
       "2023-12-31 -0.027459 -0.024982 -0.026196 -0.027796      0.021271  \n",
       "\n",
       "[348 rows x 11 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "portfolio_beta = portfolio_beta['1995-01':'2023-12']\n",
    "portfolio_beta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:           My_portfolio   R-squared:                       0.000\n",
      "Model:                            OLS   Adj. R-squared:                  0.000\n",
      "Method:                 Least Squares   F-statistic:                       nan\n",
      "Date:                Wed, 04 Dec 2024   Prob (F-statistic):                nan\n",
      "Time:                        13:04:06   Log-Likelihood:                 474.65\n",
      "No. Observations:                 348   AIC:                            -947.3\n",
      "Df Residuals:                     347   BIC:                            -943.4\n",
      "Df Model:                           0                                         \n",
      "Covariance Type:                  HAC                                         \n",
      "==============================================================================\n",
      "                 coef    std err          z      P>|z|      [0.025      0.975]\n",
      "------------------------------------------------------------------------------\n",
      "Intercept      0.0071      0.003      2.247      0.025       0.001       0.013\n",
      "==============================================================================\n",
      "Omnibus:                       30.431   Durbin-Watson:                   2.022\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):              135.354\n",
      "Skew:                          -0.067   Prob(JB):                     4.06e-30\n",
      "Kurtosis:                       6.052   Cond. No.                         1.00\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors are heteroscedasticity and autocorrelation robust (HAC) using 6 lags and without small sample correction\n"
     ]
    }
   ],
   "source": [
    "model = smf.ols('My_portfolio ~ 1',\n",
    "                 data=portfolio_beta['1995-01':'2023-12']).fit(\n",
    "                     cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model.summary())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- CAPM falls\n",
    "  - Low-Risk:Returns are not positively correlated to stock betas/riskss\n",
    "  - $\\alpha\\neq0$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>month</th>\n",
       "      <th>P1</th>\n",
       "      <th>P2</th>\n",
       "      <th>P3</th>\n",
       "      <th>P4</th>\n",
       "      <th>P5</th>\n",
       "      <th>P6</th>\n",
       "      <th>P7</th>\n",
       "      <th>P8</th>\n",
       "      <th>P9</th>\n",
       "      <th>Pmax</th>\n",
       "      <th>My_portfolio</th>\n",
       "      <th>MKT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-01-31</td>\n",
       "      <td>-0.119180</td>\n",
       "      <td>-0.072110</td>\n",
       "      <td>-0.130556</td>\n",
       "      <td>-0.124243</td>\n",
       "      <td>-0.120475</td>\n",
       "      <td>-0.126131</td>\n",
       "      <td>-0.135343</td>\n",
       "      <td>-0.145437</td>\n",
       "      <td>-0.152750</td>\n",
       "      <td>-0.155980</td>\n",
       "      <td>0.036800</td>\n",
       "      <td>-0.125017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1995-02-28</td>\n",
       "      <td>-0.009711</td>\n",
       "      <td>-0.008694</td>\n",
       "      <td>-0.010877</td>\n",
       "      <td>-0.012167</td>\n",
       "      <td>-0.006294</td>\n",
       "      <td>-0.027947</td>\n",
       "      <td>-0.032387</td>\n",
       "      <td>-0.039242</td>\n",
       "      <td>-0.041955</td>\n",
       "      <td>-0.051491</td>\n",
       "      <td>0.041780</td>\n",
       "      <td>-0.022778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1995-03-31</td>\n",
       "      <td>0.062154</td>\n",
       "      <td>0.058339</td>\n",
       "      <td>0.075692</td>\n",
       "      <td>0.104134</td>\n",
       "      <td>0.089152</td>\n",
       "      <td>0.138383</td>\n",
       "      <td>0.118963</td>\n",
       "      <td>0.147348</td>\n",
       "      <td>0.184887</td>\n",
       "      <td>0.218689</td>\n",
       "      <td>-0.156534</td>\n",
       "      <td>0.122163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1995-04-30</td>\n",
       "      <td>-0.129923</td>\n",
       "      <td>-0.109784</td>\n",
       "      <td>-0.122973</td>\n",
       "      <td>-0.096805</td>\n",
       "      <td>-0.116945</td>\n",
       "      <td>-0.130956</td>\n",
       "      <td>-0.096548</td>\n",
       "      <td>-0.094319</td>\n",
       "      <td>-0.111986</td>\n",
       "      <td>-0.121447</td>\n",
       "      <td>-0.008475</td>\n",
       "      <td>-0.113023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1995-05-31</td>\n",
       "      <td>0.078637</td>\n",
       "      <td>0.066627</td>\n",
       "      <td>0.099883</td>\n",
       "      <td>0.103732</td>\n",
       "      <td>0.156507</td>\n",
       "      <td>0.220783</td>\n",
       "      <td>0.199680</td>\n",
       "      <td>0.197807</td>\n",
       "      <td>0.245065</td>\n",
       "      <td>0.223204</td>\n",
       "      <td>-0.144567</td>\n",
       "      <td>0.166736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>-0.036412</td>\n",
       "      <td>-0.054473</td>\n",
       "      <td>-0.068013</td>\n",
       "      <td>-0.057719</td>\n",
       "      <td>-0.068990</td>\n",
       "      <td>-0.058171</td>\n",
       "      <td>-0.054705</td>\n",
       "      <td>-0.059133</td>\n",
       "      <td>-0.063060</td>\n",
       "      <td>-0.071705</td>\n",
       "      <td>0.035293</td>\n",
       "      <td>-0.056811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>2023-09-30</td>\n",
       "      <td>0.018065</td>\n",
       "      <td>0.006200</td>\n",
       "      <td>-0.007416</td>\n",
       "      <td>-0.010419</td>\n",
       "      <td>-0.004966</td>\n",
       "      <td>-0.001680</td>\n",
       "      <td>-0.017780</td>\n",
       "      <td>-0.035491</td>\n",
       "      <td>-0.016884</td>\n",
       "      <td>-0.018909</td>\n",
       "      <td>0.036975</td>\n",
       "      <td>-0.006930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>-0.026936</td>\n",
       "      <td>-0.035263</td>\n",
       "      <td>-0.035169</td>\n",
       "      <td>-0.045496</td>\n",
       "      <td>-0.020575</td>\n",
       "      <td>-0.030723</td>\n",
       "      <td>-0.040041</td>\n",
       "      <td>-0.039563</td>\n",
       "      <td>-0.019571</td>\n",
       "      <td>0.002289</td>\n",
       "      <td>-0.029226</td>\n",
       "      <td>-0.027562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>0.011564</td>\n",
       "      <td>0.005363</td>\n",
       "      <td>0.015737</td>\n",
       "      <td>0.014640</td>\n",
       "      <td>-0.001344</td>\n",
       "      <td>-0.022379</td>\n",
       "      <td>0.027644</td>\n",
       "      <td>-0.017196</td>\n",
       "      <td>-0.017168</td>\n",
       "      <td>-0.010074</td>\n",
       "      <td>0.021638</td>\n",
       "      <td>0.000549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>-0.006525</td>\n",
       "      <td>-0.023350</td>\n",
       "      <td>-0.015199</td>\n",
       "      <td>-0.026276</td>\n",
       "      <td>-0.024242</td>\n",
       "      <td>-0.024932</td>\n",
       "      <td>-0.027459</td>\n",
       "      <td>-0.024982</td>\n",
       "      <td>-0.026196</td>\n",
       "      <td>-0.027796</td>\n",
       "      <td>0.021271</td>\n",
       "      <td>-0.021244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>348 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         month        P1        P2        P3        P4        P5        P6  \\\n",
       "0   1995-01-31 -0.119180 -0.072110 -0.130556 -0.124243 -0.120475 -0.126131   \n",
       "1   1995-02-28 -0.009711 -0.008694 -0.010877 -0.012167 -0.006294 -0.027947   \n",
       "2   1995-03-31  0.062154  0.058339  0.075692  0.104134  0.089152  0.138383   \n",
       "3   1995-04-30 -0.129923 -0.109784 -0.122973 -0.096805 -0.116945 -0.130956   \n",
       "4   1995-05-31  0.078637  0.066627  0.099883  0.103732  0.156507  0.220783   \n",
       "..         ...       ...       ...       ...       ...       ...       ...   \n",
       "343 2023-08-31 -0.036412 -0.054473 -0.068013 -0.057719 -0.068990 -0.058171   \n",
       "344 2023-09-30  0.018065  0.006200 -0.007416 -0.010419 -0.004966 -0.001680   \n",
       "345 2023-10-31 -0.026936 -0.035263 -0.035169 -0.045496 -0.020575 -0.030723   \n",
       "346 2023-11-30  0.011564  0.005363  0.015737  0.014640 -0.001344 -0.022379   \n",
       "347 2023-12-31 -0.006525 -0.023350 -0.015199 -0.026276 -0.024242 -0.024932   \n",
       "\n",
       "           P7        P8        P9      Pmax  My_portfolio       MKT  \n",
       "0   -0.135343 -0.145437 -0.152750 -0.155980      0.036800 -0.125017  \n",
       "1   -0.032387 -0.039242 -0.041955 -0.051491      0.041780 -0.022778  \n",
       "2    0.118963  0.147348  0.184887  0.218689     -0.156534  0.122163  \n",
       "3   -0.096548 -0.094319 -0.111986 -0.121447     -0.008475 -0.113023  \n",
       "4    0.199680  0.197807  0.245065  0.223204     -0.144567  0.166736  \n",
       "..        ...       ...       ...       ...           ...       ...  \n",
       "343 -0.054705 -0.059133 -0.063060 -0.071705      0.035293 -0.056811  \n",
       "344 -0.017780 -0.035491 -0.016884 -0.018909      0.036975 -0.006930  \n",
       "345 -0.040041 -0.039563 -0.019571  0.002289     -0.029226 -0.027562  \n",
       "346  0.027644 -0.017196 -0.017168 -0.010074      0.021638  0.000549  \n",
       "347 -0.027459 -0.024982 -0.026196 -0.027796      0.021271 -0.021244  \n",
       "\n",
       "[348 rows x 13 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MYPORT=pd.merge(portfolio_beta,Market_ret[['month','MKT']],on='month',how='left')\n",
    "MYPORT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                            OLS Regression Results                            \n",
      "==============================================================================\n",
      "Dep. Variable:           My_portfolio   R-squared:                       0.151\n",
      "Model:                            OLS   Adj. R-squared:                  0.149\n",
      "Method:                 Least Squares   F-statistic:                     10.59\n",
      "Date:                Wed, 04 Dec 2024   Prob (F-statistic):            0.00125\n",
      "Time:                        13:04:06   Log-Likelihood:                 503.21\n",
      "No. Observations:                 348   AIC:                            -1002.\n",
      "Df Residuals:                     346   BIC:                            -994.7\n",
      "Df Model:                           1                                         \n",
      "Covariance Type:                  HAC                                         \n",
      "==============================================================================\n",
      "                 coef    std err          z      P>|z|      [0.025      0.975]\n",
      "------------------------------------------------------------------------------\n",
      "Intercept      0.0095      0.003      3.343      0.001       0.004       0.015\n",
      "MKT           -0.2928      0.090     -3.254      0.001      -0.469      -0.116\n",
      "==============================================================================\n",
      "Omnibus:                      124.866   Durbin-Watson:                   1.942\n",
      "Prob(Omnibus):                  0.000   Jarque-Bera (JB):             1097.333\n",
      "Skew:                           1.234   Prob(JB):                    5.21e-239\n",
      "Kurtosis:                      11.342   Cond. No.                         12.2\n",
      "==============================================================================\n",
      "\n",
      "Notes:\n",
      "[1] Standard Errors are heteroscedasticity and autocorrelation robust (HAC) using 6 lags and without small sample correction\n"
     ]
    }
   ],
   "source": [
    "# 回归策略到市场上\n",
    "model = smf.ols('My_portfolio ~ MKT',\n",
    "                 data=MYPORT).fit(cov_type='HAC', cov_kwds={'maxlags': 6})\n",
    "print(model.summary())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "$$\n",
    "R_{\\beta，big}-R_{\\beta,small}=-0.0095+0.2928(\\beta_{big}-\\beta_{small})*MKT\n",
    "$$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- CAPM fails\n",
    "  - Cross sectional returns should be explained by their betas 100%.\n",
    "  - Returns cannot be predicted by any other variables.\n",
    "  - Size 规模 买小公司 卖大公司\n",
    "   - Value 价值 买价值股 卖成长股"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
